/*
 * Copyright (c) MuleSoft, Inc.  All rights reserved.  http://www.mulesoft.com
 * The software in this package is published under the terms of the CPAL v1.0
 * license, a copy of which has been included with this distribution in the
 * LICENSE.txt file.
 */
package org.mule.tck.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

/**
 * Helper class for creating a derby database.
 */
public class MuleDerbyTestDatabase
{

    public static final String DERBY_PROPERTIES_FILE = "derby.properties";
    private final String databaseNameProperty;
    private String connectionString;

    /**
     * @param databaseNameProperty name of the property with the database name in derby.properties file.
     *                             To add a new database change derby.properties file.
     */
    public MuleDerbyTestDatabase(String databaseNameProperty)
    {
        this.databaseNameProperty = databaseNameProperty;
    }

    /**
     * Creates an empty database with a table named TEST.
     *
     * @throws Exception
     */
    public void startDatabase() throws Exception
    {
        String dbName = MuleDerbyTestUtils.loadDatabaseName(DERBY_PROPERTIES_FILE, databaseNameProperty);
        MuleDerbyTestUtils.defaultDerbyCleanAndInit("derby.properties", databaseNameProperty);
        connectionString = "jdbc:derby:" + dbName;
        emptyTestTable();
    }

    /**
     * Stops the created database.
     *
     * @throws SQLException
     */
    public void stopDatabase() throws SQLException
    {
        MuleDerbyTestUtils.stopDatabase();
    }

    /**
     * Remove all the rows from TEST table.
     *
     * @throws SQLException
     */
    public void emptyTestTable() throws Exception
    {
        try
        {
            execSqlUpdate("DELETE FROM TEST");
        }
        catch (Exception e)
        {
            execSqlUpdate("CREATE TABLE TEST(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,DATA VARCHAR(255), TYPE NUMERIC, ACK NUMERIC)");
        }
    }

    /**
     * Inserts a record in the TEST table.
     */
    public void insertIntoTest(String data, int type)
    {
        try
        {
            execSqlUpdate("INSERT into Test (data, type) VALUES ('" + data + "', " + type + ")");
        }
        catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

    /**
     * @return a new Connection to access the database.
     * @throws Exception
     */
    public Connection getConnection() throws Exception
    {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        return DriverManager.getConnection(connectionString);
    }

    /**
     * Executes a query against the database.
     *
     * @param sql SQL query
     * @return
     * @throws Exception
     */
    public List execSqlQuery(String sql) throws Exception
    {
        Connection con = null;
        try
        {
            con = getConnection();
            return (List) new QueryRunner().query(con, sql, new ArrayListHandler());
        }
        finally
        {
            if (con != null && !con.isClosed())
            {
                con.close();
            }
        }
    }

    /**
     * Executes an SQL statement against the database.
     * For queries use execSqlQuery.
     *
     * @param sql SQL query
     * @return
     * @throws Exception
     */
    public int execSqlUpdate(String sql) throws Exception
    {
        Connection con = null;
        try
        {
            con = getConnection();
            return new QueryRunner().update(con, sql);
        }
        finally
        {
            if (con != null && !con.isClosed())
            {
                con.close();
            }
        }
    }
}
